import java.io.File;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;


import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;

public class Test {
	public static void main(String[] args) throws Exception {
		WorkbookSettings workbookSettings = new WorkbookSettings();
		workbookSettings.setEncoding("gbk");
//		File file = new File(StringUtil.trimStr("‪E:\\QQ\\硝酸盐20180424.xls"));
		File file = new File(StringUtil.trimStr("‪E:\\QQ\\YANSHOU20180514-2^P1.XLS"));

		Workbook workbook = Workbook.getWorkbook(file,workbookSettings);
		Sheet[] sheets = workbook.getSheets();
		System.out.println(sheets.length);
		for(int i=0;i<sheets.length;i++){
			System.out.println(i);
			Sheet sheet = workbook.getSheet(0);
			String[][] xlsData = getXlsData(sheet);
			printSheet(xlsData);
		}
		
//		Sheet sheet = workbook.getSheet(0);
//		String[][] xlsData = getXlsData(sheet);
//		printSheet(xlsData);
		
	}
	
	public static void printSheet(String[][] xlsData){
		for(int i=0;i<xlsData.length;i++){
			for(int j=0;j<xlsData[i].length;j++){
				System.out.print(xlsData[i][j]+"\t");
			}
			System.out.println();
		}
		
	}
	
	public static String[][] getXlsData(Sheet st)  {
		String[][] returnArray = null;
		try {
			List<String[]> result = new ArrayList<String[]>();
			int rowSize = 0;
			if (st != null) {
				for (int rowIndex = 0; rowIndex < st.getRows(); rowIndex++) {
					Cell[] cells = st.getRow(rowIndex);
					if (cells == null) {
						continue;
					}
					int tempRowSize = cells.length + 1;
					if (tempRowSize > rowSize) {
						rowSize = tempRowSize;
					}
					String[] values = new String[rowSize];
					Arrays.fill(values, "");
					for (int i = 0; i < cells.length; i++) {
						String value = "";
						Cell cell = cells[i];
						if (cell != null) {
							if (CellType.NUMBER == cell.getType()) {
								NumberCell nc = (NumberCell) cell;
								value = nc.getContents().replace("\"", "").trim();
								if(value.contains(".")){
									String[] split = value.split("\\.");
									if(split[1].length()>=2){
										BigDecimal bd= new BigDecimal(nc.getValue());
										double   f1   =   bd.setScale(15,BigDecimal.ROUND_HALF_UP).doubleValue();
										value = f1 + "";
									}
								}
							} else if (CellType.LABEL == cell.getType()) {
								value = cell.getContents();
							} else if (CellType.DATE == cell.getType()) {
								DateCell dc = (DateCell) cell;
								Date date = dc.getDate();
								TimeZone zone = TimeZone.getTimeZone("GMT");
								SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
								sdf.setTimeZone(zone);
								value = sdf.format(date);
							} else if (CellType.EMPTY == cell.getType()) {
								continue;
							} else if (CellType.BOOLEAN == cell.getType()) {
								value = cell.getContents();
							}
						}
						if (value.contains("\n")) {
							value = value.toString().replaceAll("\n", "");
						}
						values[i] = rightTrim(value);
					}
					result.add(values);
				}
				returnArray = new String[result.size()][rowSize];
				for (int i = 0; i < returnArray.length; i++) {
					returnArray[i] = result.get(i);
				}
			} else {
			}
		} catch (Exception e) {
			
		}
		return returnArray;
	}
	public static String rightTrim(String str) {
		if (str == null) {
			return "";
		}
		int length = str.length();
		for (int i = length - 1; i >= 0; i--) {
			if (str.charAt(i) != 0x20) {
				break;
			}
			length--;
		}
		return str.substring(0, length);
	}
}
